package cn.lnexin.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

//////////////////////////////////////////////////////////////////////////////////
//		注意jar包版本,新版(应该是6.0以上)的driverClass格式不一样						//
//		old :com.mysql.jdbc.Driver												//
//		6.0+:com.mysql.cj.jdbc.Driver											//
//		url连接中需要带上serverTimezone=UTC										//
//      中国地区请使用:serverTimezone=Asia/Shanghai                               //
//////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////
//		jdbc_driver=com.mysqljdbc.Driver										//
//		jdbc_url=jdbc:mysql://localhost:3306/log?characterEncoding=utf-8		//
//		jdbc_username=root														//
//		jdbc_password=admin														//
//////////////////////////////////////////////////////////////////////////////////

/**
 * @author lnexin@aliyun.com
 * <p>
 * <li>
 * 1. 执行单条: executeStat(String sql) /executePstamt(String sql, List params)----:return boolean
 * <li>
 * 2. 批量执行: batchExecutePstamt(String sql, List<List> lists)----:return boolean 可以更改为受影响数量
 * <li>
 * 3. 单条记录查询 : selectOne(String sql, List params)----:return map 一条记录的集合
 * <li>
 * 4. 多条查询 : selectMore(String sql, List params)----:return List(map);
 * <li>
 * 5. 列名查询: selectColumns(String tanleN)----:return list(string);
 * <li>
 * 6. 建表语句 : createTableSql(String tableName, List<String> fields, String engine)----:return string;
 * <li>
 * 7. 插入语句 : getInsertSql(String tableName, List<String> fields)----:return string;
 * <li>
 * 8. 验证表是否存在: validateTableExist(String tableName)----:return boolean
 * <li>
 * 9. 关闭连接: releaseConn()
 */
public class DataBaseUtil {
    // 数据库配置文件地址
    private static final String CONFIG_PATH = "/jdbc.properties";

    private static final String DB_DRIVER;
    private static final String DB_URL;
    private static final String DB_USERNAME;
    private static final String DB_PASSWORD;

    private Connection connection;
    private PreparedStatement pstmt;
    private ResultSet resultSet;

    static {
        Properties p = new Properties();
        try {
            // 两种方式任选其一
            InputStream u = DataBaseUtil.class.getResourceAsStream(CONFIG_PATH);
            p.load(u);
        } catch (FileNotFoundException e) {
            System.err.println("Not Database configuration files !");
            e.printStackTrace();
        } catch (IOException e) {
            System.err.println("Read configuration file failure!");
            e.printStackTrace();
        }
        DB_DRIVER = p.getProperty("jdbc.driver");
        DB_URL = p.getProperty("jdbc.url");
        DB_USERNAME = p.getProperty("jdbc.username");
        DB_PASSWORD = p.getProperty("jdbc.password");
    }

    public DataBaseUtil() {
    }

    /**
     * 使用配置文件中的连接信息获取连接
     */
    public Connection getConn() {
        try {
            Class.forName(DB_DRIVER);
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (SQLException e) {
            System.err.println("Get connection failure！" + e);
        } catch (ClassNotFoundException e) {
            System.err.println("Load DB Driver failure!" + e);
        }
        return connection == null ? connection : null;
    }

    /////////////////////////////////////////////////////////////////////////////////////

    /**
     * 使用非配置文件的连接信息
     *
     * @param driver   数据路驱动
     * @param url      数据库连接
     * @param username 数据库用户名
     * @param password 数据库密码
     * @return Connection
     */
    public Connection getConn(String driver, String url, String username, String password) {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.err.println("Get connection failure！" + e);
            releaseConn();
        } catch (ClassNotFoundException e) {
            System.err.println("Load DB Driver failure!" + e);
        }
        return connection == null ? connection : null;
    }
    //////////////////////////////////////////////////////////////////////////////////////

    /**
     * 插入
     *
     * @param sql   prepareStatement格式的插入语句
     * @param param 需要插入的单个数值
     * @return 自增的情况下返回插入的主键
     * @throws SQLException
     */
    public Object insertReturnPK(String sql, Object param) throws SQLException {
        return insertReturnPK(sql, Arrays.asList(param));
    }

    /**
     * @param sql    prepareStatement格式的insert语句
     * @param params 需要插入的多个参数,以list格式
     * @return 返回插入语句的自增主键
     * @throws SQLException
     */
    public Object insertReturnPK(String sql, List params) throws SQLException {
        pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        pstmt.executeUpdate();
        resultSet = pstmt.getGeneratedKeys();
        Object pk = null;
        if (resultSet.next()) {
            pk = resultSet.getObject(1);
        }
        closePstmat();
        resultSet.close();
        return pk;
    }

    /**
     * 执行某一条语句
     *
     * @param sql   prepareStatement格式的sql语句
     * @param param 填充的参数
     * @return 返回是否执行成功
     * @throws SQLException
     */
    public boolean executePstamt(String sql, Object param) throws SQLException {
        return executePstamt(sql, Arrays.asList(param));
    }

    /**
     * 执行某一条语句
     *
     * @param sql   prepareStatement格式的sql语句
     * @param param 填充的参数列表
     * @return 返回是否执行成功
     * @throws SQLException
     */
    public boolean executePstamt(String sql, List params) throws SQLException {
        int result = -1;
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        closePstmat();
        return result > 0 ? true : false;
    }

    /**
     * 批量执行
     *
     * @param sql   prepareStatement格式的sql语句
     * @param lists 需要插入的数据列表,注:List<List> 格式,子list中为一条语句所填充的参数列表
     * @return 返回是否执行成功
     * @throws SQLException
     */
    public boolean batchExecutePstamt(String sql, List<List> lists) throws SQLException {
        connection.setAutoCommit(false);
        boolean flag = false;
        int resultNum = 0;
        pstmt = connection.prepareStatement(sql);
        if (lists != null && !lists.isEmpty()) {
            for (List<Object> cList : lists) {
                if (cList == null || cList.isEmpty()) continue;

                for (int i = 0; i < cList.size(); i++) {
                    pstmt.setObject(i + 1, cList.get(i));
                }
                pstmt.addBatch();
            }
            int[] resNum = pstmt.executeBatch();
            connection.commit();
            resultNum += resNum.length;
        }
        closePstmat();
        flag = resultNum > 0 ? true : false;
        return flag;
    }

    /**
     * 查询一条,返回结果
     *
     * @param sql   prepareStatement格式语句
     * @param param 需要的参数列表,可为空
     * @return 一个map集合
     * @throws SQLException
     */
    public Map selectOne(String sql, List params) throws SQLException {
        Map map = new LinkedHashMap();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < col_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        closeResultSet();
        closePstmat();
        return map;
    }

    /**
     * 查询多个结果的返回集
     *
     * @param sql    prepareStatement格式语句
     * @param params 需要填充的参数列表,可为空
     * @return 一个list<map>的结果集
     * @throws SQLException
     */
    public List<Map> selectMore(String sql, List params) throws SQLException {
        List<Map> list = new ArrayList<Map>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            Map map = new LinkedHashMap();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        closeResultSet();
        closePstmat();
        return list;
    }

    /**
     * 查询列名
     *
     * @param tanleN 表名称
     * @return 列名称列表
     * @throws SQLException
     */
    public List<String> selectColumns(String tanleN) throws SQLException {
        DatabaseMetaData data;
        List<String> result = new ArrayList<>();
        data = connection.getMetaData();
        ResultSet columns = data.getColumns(null, null, tanleN, null);
        while (columns.next()) {
            result.add(columns.getString("COLUMN_NAME"));
        }
        columns.close();
        return result;
    }

    /**
     * 获取插入语句
     *
     * @param tableName
     * @param fields
     * @param isIGNORE  是否去重复
     * @return
     */
    public static String getInsertSql(String tableName, List<String> fields, boolean isIGNORE) {
        StringBuffer sb = new StringBuffer();
        if (isIGNORE) {
            sb.append("INSERT IGNORE INTO `").append(tableName).append("` ");
        } else {
            sb.append("INSERT INTO `").append(tableName).append("` ");
        }

        StringBuffer field = new StringBuffer("(");
        StringBuffer value = new StringBuffer("(");
        int sign = 0;
        for (String f : fields) {
            if (sign > 0) {
                field.append(",");
                value.append(",");
            }
            field.append("`").append(f).append("`");
            value.append("?");
            sign++;
        }
        field.append(")");
        value.append(")");
        sb.append(field).append(" values ").append(value).append(";");
        return sb.toString();
    }

    /**
     * 获取建表语句
     *
     * @param tableName
     * @param fields    Map<String,String> 结构为[字段名,字段数据类型],如:[{name=varchar(64),lastname=varchar(64)}]
     * @param engine    :InnoDB/MyISAM
     * @return create sql
     */
    public static String createTableSql(String tableName, Map<String, String> fields, String engine) {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");
        int sign = 0;
        for (Entry<String, String> entry : fields.entrySet()) {
            String field = entry.getKey();
            String type = entry.getValue();
            if (sign > 0) sb.append(",");
            sb.append("`").append(field).append("` ");
            sb.append(type).append(" null default null");
        }
        sb.append(")");
        sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");
        return sb.toString();
    }

    /**
     * 获取建表语句
     *
     * @param tableName
     * @param fields           一个list<string> 的字段列表
     * @param engine           数据库引擎类型
     * @param defaultFieldType 默认数据字段的类型,如:varchar(64)
     * @return
     */
    public static String createTableSql(String tableName, List<String> fields, String engine, String defaultFieldType) {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE IF NOT EXISTS `").append(tableName).append("` (");

        int sign = 0;
        for (String field : fields) {
            if (sign > 0) sb.append(",");
            sb.append("`").append(field).append("` ");
            sb.append(defaultFieldType).append(" null default null");
        }

        sb.append(")");
        sb.append(" COLLATE='utf8_general_ci' ").append(" ENGINE=").append(engine).append(";");
        return sb.toString();
    }

    /**
     * 验证表是否存在
     *
     * @param tableName 表名称
     * @return true/false
     */
    public boolean validateTableExist(String tableName) {
        boolean flag = false;
        try {
            DatabaseMetaData meta = connection.getMetaData();
            String type[] = {"TABLE"};
            ResultSet rs = meta.getTables(null, null, tableName, type);
            flag = rs.next();
        } catch (SQLException e) {
            System.err.println("ERROR! validateTableExist failure! msg:[" + e + "]");
            e.printStackTrace();
        }
        return flag;
    }
    
	public static String getAlertFieldSql(String tableName, String newField) {
		StringBuilder ab = new StringBuilder();
		ab.append("ALTER TABLE `").append(tableName).append("` ");
		ab.append("add column ");
		ab.append("`").append(newField).append("` varchar(256) default null;");
		return ab.toString();
	}

    /*
     * 释放链接
     */
    public void releaseConn() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void closeResultSet() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void closePstmat() {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
